package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import CONEXAO.Conexao;
import CORE.GenericDAO;
import DTO.ComentarioDTO;
import DTO.ObraDTO;

public class ComentarioDAO extends GenericDAO<ComentarioDTO> {
	
	public ComentarioDAO() {
		super("comentario", ComentarioDTO.class); 
	}

	public Integer pesquisarRankDaObra(ObraDTO obra){
		try {
			Integer retorno = 0;
			Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();   			
			sql.append("SELECT AVG(rank) FROM ");
			sql.append(tabela);
			sql.append(" WHERE obra = (?) ");			
			PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setLong(1, obra.getCodigo());
                        
            ResultSet rs = ps.executeQuery();            
            if(rs.next()){
            	retorno = rs.getInt(1);
            }
            
            ps.close();
            rs.close();                        
            conn.close();
            
            return retorno;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public List<ComentarioDTO> pesquisarComentariosDaObra(ObraDTO obra){
		try {
			List<ComentarioDTO> comentarios = new ArrayList<>();
			Connection conn = Conexao.conectar(BANCO);
            StringBuilder sql = new StringBuilder();   			
			sql.append("SELECT c.* ");
			sql.append("FROM comentario c, obra o ");
			sql.append("WHERE c.obra = o.codigo ");
			sql.append("AND o.codigo = (?)");			
			PreparedStatement ps = conn.prepareStatement(sql.toString());
            ps.setLong(1, obra.getCodigo());
                        
            ResultSet rs = ps.executeQuery();            
                        
            while(rs.next()){
            	comentarios.add(criarObjeto(rs));
            }
            
            ps.close();
            rs.close();                        
            conn.close();
			return comentarios;
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public ComentarioDTO criarObjeto(ResultSet rs) throws SQLException {
		ComentarioDTO comentario = new ComentarioDTO();
		comentario.setCodigo(rs.getLong(1));
		comentario.setDescricao(rs.getString(2));
		comentario.setRank(rs.getInt(3));
		comentario.setUsuario(new UsuarioDAO().pesquisarPorCodigo(rs.getLong(4)));
		comentario.setObra(new ObraDAO().pesquisarPorCodigo(rs.getLong(5)));
		return comentario;
	}

	@Override
	public void setarPSExcluir(PreparedStatement ps, ComentarioDTO comentario) throws SQLException {
		ps.setLong(1, comentario.getCodigo());
	}

	@Override
	public void setarPSAlterar(PreparedStatement ps, ComentarioDTO comentario) throws SQLException {
		setarPSIncluir(ps, comentario);
		ps.setLong(5, comentario.getCodigo());
	}

	@Override
	public void setarPSIncluir(PreparedStatement ps, ComentarioDTO comentario) throws SQLException {
		ps.setString(1, comentario.getDescricao());
		ps.setInt(2, comentario.getRank());
		ps.setLong(3, comentario.getUsuario().getCodigo());
		ps.setLong(4, comentario.getObra().getCodigo());
	}
}